import pandas as pd
import re
from collections import defaultdict

# 读取Excel文件
file_path = "Conference_Papers.xlsx"
df = pd.read_excel(file_path, sheet_name="Papers")

# 定义领域关键词和复杂逻辑
domain_keywords = {
    "UI测试": {
        "patterns": [
            # 动态分析相关
            r"mobile.*dynamic.*analysis",
            r"android.*dynamic.*analysis",
            r"ios.*dynamic.*analysis",
            r"app.*dynamic.*analysis",
            r"dynamic.*analysis.*mobile",
            r"dynamic.*analysis.*android",
            r"dynamic.*analysis.*ios",
            r"dynamic.*analysis.*app",

            # UI测试相关
            r"mobile.*ui.*test",
            r"android.*ui.*test",
            r"ios.*ui.*test",
            r"app.*ui.*test",
        ],
        "description": "包含(ui test或dynamic analysis)并且包含app"
    },
    "性能分析": {
        "patterns": [r"app.*performance"]
    },
    "程序分析/代码修复": [ "static analysis", "code repair"]
}

# 预处理：合并Keywords和Abstract为一个文本字段
df["Text"] = df["Keywords"].fillna("") + " " + df["Abstract"].fillna("")

# 转换为小写以便匹配
df["Text"] = df["Text"].str.lower()

# 为每个领域创建标记列
for domain, keywords in domain_keywords.items():
    if isinstance(keywords, dict):  # 处理复杂逻辑
        patterns = keywords["patterns"]
        df[domain] = False

        # 对每个模式进行检查
        for pattern in patterns:
            df[domain] = df[domain] | df["Text"].str.contains(pattern, na=False, regex=True)
    else:  # 处理简单关键词列表
        pattern = "|".join(keywords)
        df[domain] = df["Text"].str.contains(pattern, na=False)

# 提取作者列和单位列
author_columns = [col for col in df.columns if col.startswith("Author_")]
affiliation_columns = [col for col in df.columns if col.startswith("Affiliations_")]

# 合并所有作者到一个列表，并记录每个作者的单位
df["All_Authors"] = df[author_columns].apply(
    lambda row: [str(author).strip() for author in row if pd.notna(author) and str(author).strip() != ""],
    axis=1
)

# 创建作者到单位的映射
author_affiliation_map = {}
for _, row in df.iterrows():
    for i, author_col in enumerate(author_columns):
        if i < len(affiliation_columns) and pd.notna(row[author_col]) and str(row[author_col]).strip() != "":
            author = str(row[author_col]).strip()
            affiliation = str(row[affiliation_columns[i]]).strip() if pd.notna(
                row[affiliation_columns[i]]) else "Unknown"

            # 如果作者已存在，合并单位信息
            if author in author_affiliation_map:
                if affiliation not in author_affiliation_map[author]:
                    author_affiliation_map[author].append(affiliation)
            else:
                author_affiliation_map[author] = [affiliation]


# 根据单位推断国家
def infer_country(affiliation):
    affiliation_lower = affiliation.lower()

    # 国家关键词映射
    country_keywords = {
        "China": ["china", "chinese", "beijing", "shanghai", "shenzhen", "hong kong", "taiwan"],
        "USA": ["usa", "united states", "america", "us", "california", "texas", "massachusetts", "new york"],
        "UK": ["uk", "united kingdom", "england", "london", "oxford", "cambridge"],
        "Germany": ["germany", "german", "berlin", "munich", "technische universität"],
        "Canada": ["canada", "toronto", "waterloo", "vancouver"],
        "Australia": ["australia", "sydney", "melbourne"],
        "Singapore": ["singapore"],
        "Japan": ["japan", "tokyo", "kyoto"],
        "South Korea": ["korea", "seoul"],
        "France": ["france", "paris"],
        "Italy": ["italy", "rome", "milan"],
        "Netherlands": ["netherlands", "amsterdam"],
        "Switzerland": ["switzerland", "zurich"],
        "India": ["india", "delhi", "bombay"],
        "Brazil": ["brazil", "rio", "sao paulo"],
        "Russia": ["russia", "moscow"]
    }

    for country, keywords in country_keywords.items():
        for keyword in keywords:
            if keyword in affiliation_lower:
                return country

    return "Unknown"


# 创建三个领域的DataFrame
ui_test_df = df[df["UI测试"]].copy()
performance_df = df[df["性能分析"]].copy()
program_analysis_df = df[df["程序分析/代码修复"]].copy()

# 创建作者-论文-领域关系表
author_paper_domain = []

for _, row in df.iterrows():
    title = row["Title"]
    domains = []
    if row["UI测试"]:
        domains.append("UI测试")
    if row["性能分析"]:
        domains.append("性能分析")
    if row["程序分析/代码修复"]:
        domains.append("程序分析/代码修复")

    for author in row["All_Authors"]:
        for domain in domains:
            author_paper_domain.append({
                "作者": author,
                "Title": title,
                "领域": domain
            })

author_domain_df = pd.DataFrame(author_paper_domain)

# 创建作者领域统计表
author_stats = defaultdict(lambda: {"UI测试": 0, "性能分析": 0, "程序分析/代码修复": 0})

for _, row in author_domain_df.iterrows():
    author = row["作者"]
    domain = row["领域"]
    author_stats[author][domain] += 1

stats_data = []
for author, counts in author_stats.items():
    # 获取作者的单位和国家
    affiliations = author_affiliation_map.get(author, ["Unknown"])
    countries = [infer_country(aff) for aff in affiliations]

    stats_data.append({
        "作者": author,
        "单位": "; ".join(affiliations),
        "国家": "; ".join(set(countries)),  # 去重
        "UI测试论文数": counts["UI测试"],
        "性能分析论文数": counts["性能分析"],
        "程序分析/代码修复论文数": counts["程序分析/代码修复"]
    })

stats_df = pd.DataFrame(stats_data)

# 找出在三个领域都有论文的作者
authors_in_all_domains = stats_df[
    (stats_df["UI测试论文数"] > 0) &
    (stats_df["性能分析论文数"] > 0) &
    (stats_df["程序分析/代码修复论文数"] > 0)
    ]["作者"].tolist()

# 保存到Excel文件的不同sheet中
with pd.ExcelWriter("Conference_Papers_Analysis.xlsx") as writer:
    ui_test_df.to_excel(writer, sheet_name="UI测试", index=False)
    performance_df.to_excel(writer, sheet_name="性能分析", index=False)
    program_analysis_df.to_excel(writer, sheet_name="程序分析_代码修复", index=False)
    author_domain_df.to_excel(writer, sheet_name="作者领域分布", index=False)
    stats_df.to_excel(writer, sheet_name="作者领域统计", index=False)

print("处理完成！结果已保存到 Conference_Papers_Analysis.xlsx")
print(f"UI测试领域论文数: {len(ui_test_df)}")
print(f"性能分析领域论文数: {len(performance_df)}")
print(f"程序分析/代码修复领域论文数: {len(program_analysis_df)}")
print(f"作者领域分布记录数: {len(author_domain_df)}")
print(f"统计作者数: {len(stats_df)}")

# 打印在三个领域都有论文的作者
if authors_in_all_domains:
    print(f"\n在三个领域都有论文的作者: {len(authors_in_all_domains)}")
    for author in authors_in_all_domains:
        author_info = stats_df[stats_df["作者"] == author].iloc[0]
        print(f"- {author} (单位: {author_info['单位']}, 国家: {author_info['国家']})")
else:
    print("\n没有作者在三个领域都有论文")

